package com.animal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;

import com.animal.bean.ExpBean;

public class ExpManage {

	public static int selectNum(){
		int n=0;
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql = "select count(*) from animalfile09_laboratoryarchive";
			rs = stat.executeQuery(sql);
			while(rs.next()){
				n = rs.getInt(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("select数据库成功获取!");
			Connect.close2(con, stat);
		}
		return n;
	}
	public static boolean updateState(String FileNumber,String FileState){
		boolean issuc = false;
		Connection con =  Connect.getCon();
		Statement stat =null;
		try{
			stat = con.createStatement();
			String sql = "update animalfile09_laboratoryarchive set FileState='"+FileState+"' where FileNumber = '"+FileNumber+"'";
			int count=stat.executeUpdate(sql);
			if(count>0)
				issuc = true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("更新数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("update数据库成功获取!");
			Connect.close2(con, stat);
		}
		return issuc;
	}
	
	public static boolean delete(String FileNumber){
		boolean issuc = false;
		Connection con =  Connect.getCon();
		Statement stat =null;
		try{
			stat = con.createStatement();
			String sql = "delete from animalfile09_laboratoryarchive where FileNumber = '"+FileNumber+"'";
			int count=stat.executeUpdate(sql);
			if(count>0)
				issuc = true;	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("删除数据出错!");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("delete数据库成功获取!");
			Connect.close2(con, stat);
		}
		return issuc;
	}
	
	public static Collection selectAll(){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql = "select * from animalfile09_laboratoryarchive";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				
				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				//Timestamp ExperimentDate = (rs.getTimestamp("ExperimentDate").equals(null)) ? new Timestamp(System.currentTimeMillis()):rs.getTimestamp("ExperimentDate");
				//Timestamp FillingDate = (rs.getTimestamp("FillingDate")==null) ? Timestamp.valueOf("2000-00-00 00:00:00"):rs.getTimestamp("FillingDate");
				String FileState = (rs.getString("FileState")==null) ? "null" : rs.getString("FileState");
				Timestamp ExperimentDate = rs.getTimestamp("ExperimentDate");
				Timestamp FillingDate = rs.getTimestamp("FillingDate");

				ExpBean exp = new ExpBean();
				exp.setFileNumber(FileNumber);
				exp.setFileTitle(FileTitle);
				exp.setUserID(UserID);
				exp.setAnimalID(AnimalID);
				exp.setExperimentDate(ExperimentDate);
				exp.setFillingDate(FillingDate);
				exp.setFileState(FileState);
/*				
				Date d;
				Time t;
				d = rs.getDate("ExperimentDate");
				t = rs.getTime("ExperimentDate");
				exp.setExperimentDate(d.toString()+""+t.toString());
				d = rs.getDate("FillingDate");
				t = rs.getTime("FillingDate");
				exp.setFillingDate(d.toString()+""+t.toString());
*/
				
				list.add(exp);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, stat);
		}
		return list;
	}
	public static Collection selectLike(String filenumber,String filetitle,String userid,String animalid){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		try{
			stat = con.createStatement();
			String sql = "select * from animalfile09_laboratoryarchive where FileNumber like '%"+filenumber+"%' and FileTitle like '%"
						+filetitle+"%' and UserID like '%"+userid+"%' and AnimalID like '%"+animalid+"%'";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				
				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				//Timestamp ExperimentDate = (rs.getTimestamp("ExperimentDate").equals(null)) ? new Timestamp(System.currentTimeMillis()):rs.getTimestamp("ExperimentDate");
				//Timestamp FillingDate = (rs.getTimestamp("FillingDate")==null) ? Timestamp.valueOf("2000-00-00 00:00:00"):rs.getTimestamp("FillingDate");
				String FileState = (rs.getString("FileState")==null) ? "null" : rs.getString("FileState");
				Timestamp ExperimentDate = rs.getTimestamp("ExperimentDate");
				Timestamp FillingDate = rs.getTimestamp("FillingDate");

				ExpBean exp = new ExpBean();
				exp.setFileNumber(FileNumber);
				exp.setFileTitle(FileTitle);
				exp.setUserID(UserID);
				exp.setAnimalID(AnimalID);
				exp.setExperimentDate(ExperimentDate);
				exp.setFillingDate(FillingDate);
				exp.setFileState(FileState);
				
				list.add(exp);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！"); 
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, stat);
		}
		return list;
	}
	
	public static Collection selectAllof(int startIndex){
		Collection list = new ArrayList();
		Connection con =  Connect.getCon();
		PreparedStatement pre = null;
		ResultSet rs = null;
		try{

			String sql = "select * from animalfile09_laboratoryarchive limit ?,?";
			pre=con.prepareStatement(sql);
			pre.setInt(1,startIndex);
			pre.setInt(2,10);
			rs = pre.executeQuery();

			while(rs.next()){
				
				String FileNumber = (rs.getString("FileNumber")==null) ? "null" : rs.getString("FileNumber");
				String FileTitle = (rs.getString("FileTitle")==null) ? "null" : rs.getString("FileTitle");
				String UserID = (rs.getString("UserID")==null) ? "null" : rs.getString("UserID");
				String AnimalID = (rs.getString("AnimalID")==null) ? "null" : rs.getString("AnimalID");
				//Timestamp ExperimentDate = (rs.getTimestamp("ExperimentDate").equals(null)) ? new Timestamp(System.currentTimeMillis()):rs.getTimestamp("ExperimentDate");
				//Timestamp FillingDate = (rs.getTimestamp("FillingDate")==null) ? Timestamp.valueOf("2000-00-00 00:00:00"):rs.getTimestamp("FillingDate");
				String FileState = (rs.getString("FileState")==null) ? "未审核" : rs.getString("FileState");
				Timestamp ExperimentDate = rs.getTimestamp("ExperimentDate");
				Timestamp FillingDate = rs.getTimestamp("FillingDate");

				ExpBean exp = new ExpBean();
				exp.setFileNumber(FileNumber);
				exp.setFileTitle(FileTitle);
				exp.setUserID(UserID);
				exp.setAnimalID(AnimalID);
				exp.setExperimentDate(ExperimentDate);
				exp.setFillingDate(FillingDate);
				exp.setFileState(FileState);
				
				list.add(exp);
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			System.out.println("数据库成功获取！");
			Connect.close3(rs, con, pre);
		}
		return list;
	}
	@SuppressWarnings("finally")
	public static int counts() {
		Connection con =  Connect.getCon();
		Statement stat =null;
		ResultSet rs = null;
		int res = 0;
		try{
			stat = con.createStatement();
			String sql = "select count(FileNumber) from animalfile09_laboratoryarchive";
			rs = stat.executeQuery(sql);

			while(rs.next()){
				res =Integer.valueOf(rs.getInt("count(FileNumber)"));
			}
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("查询信息出错！");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally {
			return res;
		}
	}
	

}